﻿using fastNpoi.Attributes;
using fastNpoi.Interface;
using fastNpoi.Model;
using fastNpoi.Utils;
using Newtonsoft.Json.Linq;
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Eval;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace fastNpoi.Impl
{
    public class FastNpoi<T> : IFastNpoi<T> where T : class,new()
    {
        private IWorkbook Workbook=null;
        private ISheet Sheet = null;
        private int HeaderIndex = 0;
        private int ColumnStartIndex = 0;
        private int EveryRowSkipNumber = 1;
        private int EveryColumnSkipNumber = 1;
        private int HeaderSkipNumber = 1;
        private int PageLimit = 2000;
        private bool IsOrderby = true;
        private bool IsWriteHeader = true;
        private IDataOperation DataOperation = new DefaultOperation();
        private List<Action<T>> ActionList = new List<Action<T>>();
        private Dictionary<PropertyInfo,NpoiAttribute> ExtraInfo=new Dictionary<PropertyInfo, NpoiAttribute>();
        private Dictionary<int, List<CellRangeAddress>> MergeRecord = new Dictionary<int, List<CellRangeAddress>>()
        {
            {-1,new List<CellRangeAddress>() }
        };
        private List<CommentModel> CommentList = new List<CommentModel>();
        private List<Action<ISheet>> SheetActionList = new List<Action<ISheet>>();
        private ICellStyle HeaderStyle = null;
        private ICellStyle DataStyle=null;
        private ICellStyle CommentCellStyle = null;
        private List<T> DataList=new List<T>();
        private bool CommentVisible = false;
        private Dictionary<PropertyInfo, Dictionary<string, string>> JsonMapping = new Dictionary<PropertyInfo, Dictionary<string, string>>();


        public IFastNpoi<T> Clone()
        {
            return new FastNpoi<T>()
            {
                Workbook = this.Workbook,
                Sheet = this.Sheet,
                HeaderIndex = this.HeaderIndex,
                ColumnStartIndex= this.ColumnStartIndex,
                EveryRowSkipNumber= this.EveryRowSkipNumber,
                EveryColumnSkipNumber=this.EveryColumnSkipNumber,
                HeaderSkipNumber=this.HeaderSkipNumber,
                IsOrderby = this.IsOrderby,
                IsWriteHeader = this.IsWriteHeader,
                DataOperation = this.DataOperation,
                ActionList = this.ActionList,
                ExtraInfo = this.ExtraInfo,
                MergeRecord = this.MergeRecord,
                CommentList = this.CommentList,
                SheetActionList = this.SheetActionList,
                HeaderStyle=this.HeaderStyle,
                DataStyle=this.DataStyle,
                CommentCellStyle=this.CommentCellStyle,
                DataList=this.DataList
            };
        }
        public IFastNpoi<T> CreateWorkbook()
        {
            this.Workbook = new XSSFWorkbook();
            return this;
        }

        public IFastNpoi<T> CreateSheet(string name)
        {
            this.Sheet=this.Workbook.CreateSheet(name);
            return this;
        }

        public IFastNpoi<T> CreateSheet()
        {
            this.Sheet = this.Workbook.CreateSheet();
            return this;
        }

        public IFastNpoi<T> InitWorkbook(string path)
        {
            this.Workbook = WorkbookFactory.Create(path);
            return this;
        }

        public IFastNpoi<T> InitWorkbook(Stream stream)
        {
            this.Workbook = WorkbookFactory.Create(stream);
            return this;
        }
        public IFastNpoi<T> GetSheet(int index = 0)
        {
            this.Sheet = this.Workbook.GetSheetAt(index);
            return this;
        }

        public IFastNpoi<T> GetSheet(string name)
        {
            this.Sheet = this.Workbook.GetSheet(name);
            return this;
        }

        public IFastNpoi<T> SetDataOperation(IDataOperation operation)
        {
            this.DataOperation = operation;
            return this;
        }
        public IFastNpoi<T> DataMapper(Action<T> action)
        {
            this.ActionList.Add(action);
            return this;
        }

        public IFastNpoi<T> DisableOrderby()
        {
            this.IsOrderby = false;
            return this;
        }
        public IFastNpoi<T> DisableHeader()
        {
            this.IsWriteHeader = false;
            return this;
        }

        public Dictionary<string, int> GetHeader(int sheetIndex=0)
        {
            Dictionary<string, int> header = new Dictionary<string, int>();
            ISheet sheet = this.Workbook.GetSheetAt(sheetIndex);
            IRow row = sheet.GetRow(this.HeaderIndex);
            int columnNum = row.LastCellNum;
            for(int i=0;i<columnNum; i++)
            {
                ICell cell = row.GetCell(i);
                if (cell == null)
                {
                    continue;
                }
                header.Add(cell.StringCellValue, i);
            }
            return header;
        }
        public IFastNpoi<T> HeaderMapper(string columnName, string propertyName, int width = 0, OperationType operationType = OperationType.Import_Export, int position = 999, bool mergeCell = false)
        {
            PropertyInfo property = typeof(T).GetProperty(propertyName);
            NpoiAttribute attribute = new NpoiAttribute(columnName, width, position, operationType);
            this.ExtraInfo.Add(property, attribute);
            return this;
        }

        
        public IFastNpoi<T> HeaderMapper(string columnName, Expression<Func<T, object>> columnExpression, int width = 0, OperationType operationType = OperationType.Import_Export, int position = 999, bool mergeCell = false)
        {
            string propertyName = FastNpoiUtils.GetExpPropertyName(columnExpression.Body)?.ToString();
            PropertyInfo property = typeof(T).GetProperty(propertyName);
            NpoiAttribute attribute = new NpoiAttribute(columnName, width, position, operationType);
            this.ExtraInfo.Add(property, attribute);
            return this;
        }
        public IFastNpoi<T> SetJsonMapping(Expression<Func<T, object>> columnExpression, Dictionary<string, string> map)
        {
            string propertyName = FastNpoiUtils.GetExpPropertyName(columnExpression.Body)?.ToString();
            PropertyInfo property = typeof(T).GetProperty(propertyName);
            this.JsonMapping.Add(property, map);
            return this;
        }

        public IFastNpoi<T> SetJsonMapping(string columnName, Dictionary<string, string> map)
        {
            PropertyInfo property = typeof(T).GetProperty(columnName);
            this.JsonMapping.Add(property, map);
            return this;
        }

        public IFastNpoi<T> SetHeaderIndex(int headerIndex)
        {
            this.HeaderIndex = headerIndex;
            return this;
        }
        public IFastNpoi<T> SetRowSkipNumber(int offset)
        {
            this.EveryRowSkipNumber = offset;
            return this;
        }

        public IFastNpoi<T> SetColumnSkipNumber(int offset)
        {
            this.EveryColumnSkipNumber = offset;
            return this;
        }
        public IFastNpoi<T> SetHeaderSkipNumber(int offset)
        {
            this.HeaderSkipNumber = offset;
            return this;
        }
        public IFastNpoi<T> SetColumnStartIndex(int columnStartIndex)
        {
            this.ColumnStartIndex=columnStartIndex;
            return this;
        }
        public IFastNpoi<T> SetHeaderStyle(Func<IWorkbook, ICellStyle> styleFunc)
        {
            this.HeaderStyle = styleFunc.Invoke(this.Workbook);
            return this;
        }

        public IFastNpoi<T> SetDataStyle(Func<IWorkbook, ICellStyle> styleFunc)
        {
            this.DataStyle = styleFunc.Invoke(this.Workbook);
            return this;
        }
        public IFastNpoi<T> SetCommentCellStyle(Func<IWorkbook, ICellStyle> styleFunc)
        {
            this.CommentCellStyle = styleFunc.Invoke(this.Workbook);
            return this;
        }
        public IFastNpoi<T> SetCommentVisable(bool visable)
        {
            this.CommentVisible = visable;
            return this;
        }
        public IFastNpoi<T> SetPageLimit(int pageLimit)
        {
            this.PageLimit = pageLimit;
            return this;
        }

        public IFastNpoi<T> MergeCell(int startRowIndex, int endRowIndex, int startColumnIndex, int endColumnIndex,int sheetIndex=-1)
        {
            CellRangeAddress cellRangeAddress=new CellRangeAddress(startRowIndex,endRowIndex,startColumnIndex,endColumnIndex);
            var sheetRecord = this.MergeRecord[sheetIndex];
            sheetRecord.Add(cellRangeAddress);
            return this;
        }
        public IFastNpoi<T> CommentCell(List<CommentModel> commentModels)
        {
            this.CommentList.AddRange(commentModels);
            return this;
        }

        public IFastNpoi<T> CommentCell(Func<Dictionary<string, int>, List<CommentModel>> func)
        {
            var header = GetHeader();
            this.CommentList.AddRange(func.Invoke(header));
            return this;
        }
        public byte[] ToByte()
        {
            ExecuteSheetAction();
            using (var stream = new MemoryStream())
            {
                this.Workbook.Write(stream);
                return stream.ToArray();
            }
        }

       
        public string ToFile(string dirPath)
        {
            ExecuteSheetAction();
            if (!Directory.Exists(dirPath))
            {
                Directory.CreateDirectory(dirPath);
            }
            string fileName = $"{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";
            while (File.Exists(Path.Combine(dirPath, fileName)))
            {
                fileName = $"{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";
            }
            using (FileStream fs = File.Open(Path.Combine(dirPath, fileName), FileMode.Create))
            {
                this.Workbook.Write(fs);
            }
            return fileName;
        }

        public List<T> ToList()
        {
            var dic = FastNpoiUtils.GetHeaderAttrDic<T>(OperationType.Import,this.IsOrderby,this.ExtraInfo);
            var list = new List<T>();
            int startIndex = HeaderIndex + this.HeaderSkipNumber;
            var header = GetHeader();
            int rowNum = Sheet.LastRowNum;
            for(int i= startIndex; i< rowNum+startIndex; i++)
            {
                IRow row = this.Sheet.GetRow(i);
                if (row == null)
                {
                    continue;
                }
                else
                {
                    T entity=Activator.CreateInstance<T>();
                    foreach (var keyValue in header)
                    {
                        ICell cell = row.GetCell(keyValue.Value);
                        if (cell == null)
                        {
                            continue;
                        }
                        else
                        {
                            object cellValue = FastNpoiUtils.GetCellValue(cell);
                            if(dic.TryGetValue(keyValue.Key,out var result))
                            {
                                try
                                {
                                    DataOperation.SetValue(entity, result.prop, cellValue);
                                }
                                catch
                                {
                                    throw new Exception($"{startIndex + 1}行数据的{keyValue.Key}列赋值失败!");
                                }
                            }  
                        }
                    }
                    foreach(var action in this.ActionList)
                    {
                        action(entity);
                    }
                    list.Add(entity);
                }
            }
            return list;
        }


        public IFastNpoi<T> SetDataList(List<T> list)
        {
            this.DataList = list;
            return this;
        }

        public IFastNpoi<T> SheetMapper(Action<ISheet> action)
        {
            this.SheetActionList.Add(action);
            return this;
        }
        private void ExecuteSheetAction()
        {
            if (PageLimit < 0 || PageLimit >= 65535 - this.HeaderIndex)
            {
                throw new Exception("每页条数不能小于0且不能大于65535减去表头的偏移量！");
            }
            var dic = FastNpoiUtils.GetHeaderAttrDic<T>(OperationType.Export, this.IsOrderby, this.ExtraInfo);
            int page;
            if (PageLimit == 0)
            {
                page = 1;
            }
            else
            {
                page = (this.DataList.Count()+ PageLimit-1) / PageLimit;
            }
            for(int p = 1; p <= page; p++)
            {
                ISheet sheet;
                Dictionary<int, int> mergeCache = new Dictionary<int, int>();
                this.MergeRecord.Add(p - 1, new List<CellRangeAddress>());
                if (p == 1)
                {
                    sheet = this.Sheet;
                }
                else
                {
                    sheet = this.Workbook.CreateSheet();
                }
                if (this.IsWriteHeader)
                {
                    IRow headerRow = sheet.CreateRow(this.HeaderIndex);
                    var DataHeaderList = dic.Keys.ToArray();
                    //静态表头赋值
                    for (int i = 0; i < dic.Keys.Count; i++)
                    {
                        int cellIndex = ColumnStartIndex + (i * EveryColumnSkipNumber);
                        ICell headerCell = headerRow.CreateCell(cellIndex);
                        var attr = dic[DataHeaderList[i]].attr;
                        if (attr.ColumnWidth != 0)
                        {
                            sheet.SetColumnWidth(cellIndex, attr.ColumnWidth);
                        }
                        headerCell.SetCellValue(DataHeaderList[i]);
                        if (this.HeaderStyle != null)
                        {
                            headerCell.CellStyle = this.HeaderStyle;
                        }
                    }
                }
                //获取静态header的列头和位置
                var header = GetHeader(p-1);
                var ExcelHeaderList = header.Keys.ToArray();
                //Json表头写入
                var jsonPropertys= JsonMapping.Keys.ToArray();
                var staticColumnIndex = header.Values.Max();
                for (int i = 0; i < JsonMapping.Count; i++)
                {
                    IRow headerRow = sheet.GetRow(this.HeaderIndex);
                    var dynamicHeaderKeys = JsonMapping[jsonPropertys[i]].Keys.ToArray();
                    for (int j = 0; j < dynamicHeaderKeys.Count(); j++)
                    {
                        int cellIndex = staticColumnIndex + ColumnStartIndex + (i + 1) * (j + 1) * EveryColumnSkipNumber;
                        ICell headerCell = headerRow.CreateCell(cellIndex);
                        headerCell.SetCellValue(dynamicHeaderKeys[j]);
                    }
                }
                //数据赋值
                for (int i = (p-1)*PageLimit; i < (p*PageLimit>this.DataList.Count? this.DataList.Count: p * PageLimit); i++)
                {
                    var data = this.DataList[i];
                    if (data == null)
                    {
                        continue;
                    }
                    foreach (var action in ActionList)
                    {
                        action.Invoke(data);
                    }
                    int rowIndex = this.HeaderIndex + HeaderSkipNumber + (i * EveryRowSkipNumber)-(this.PageLimit*(p-1));
                    IRow row = sheet.CreateRow(rowIndex);
                    //静态数据赋值
                    for (int j = 0; j < ExcelHeaderList.Count(); j++)
                    {
                        int cellIndex = header[ExcelHeaderList[j]];
                        ICell cell = row.CreateCell(cellIndex);
                        var dicValue = dic[ExcelHeaderList[j]];
                        object value = DataOperation.GetValue(data, dicValue.prop);
                        if (value == null)
                        {
                            continue;
                        }
                        cell.SetCellValue(value?.ToString());
                        if (this.DataStyle != null)
                        {
                            cell.CellStyle = DataStyle;
                        }
                        if (dicValue.attr.MergeCell)
                        {
                            int lastRowIndex = this.HeaderIndex + HeaderSkipNumber + ((i - 1) * EveryRowSkipNumber) - (this.PageLimit * (p - 1));
                            if (i-(this.PageLimit * (p - 1)) == 0)
                            {
                                mergeCache.Add(cellIndex, this.HeaderIndex + HeaderSkipNumber);
                            }
                            else
                            {
                                object preValue = DataOperation.GetValue(this.DataList[i - 1], dicValue.prop);
                                if (!Equals(preValue, value))
                                {
                                    if (mergeCache[cellIndex] != lastRowIndex)
                                    {
                                        MergeCell(mergeCache[cellIndex], lastRowIndex, cellIndex, cellIndex,p-1);
                                    }

                                    mergeCache[cellIndex] = rowIndex;
                                }
                                else
                                {
                                    //如果最后一行也需要合并 特殊处理
                                    if (i == (p*PageLimit)-1)
                                    {
                                        MergeCell(mergeCache[cellIndex], rowIndex, cellIndex, cellIndex,p-1);
                                    }
                                }

                            }
                        }
                    }
                    //动态数据赋值
                    for (int z = 0; z < jsonPropertys.Count(); z++)
                    {
                        var propKey = jsonPropertys[z];
                        var map = JsonMapping[propKey];
                        var mapkeys = map.Keys.ToArray();
                        try
                        {
                            object jsonValue = DataOperation.GetValue(data, propKey);
                            if (jsonValue == null||string.IsNullOrWhiteSpace(jsonValue.ToString()))
                            {
                                break;
                            }
                            JObject obj = JObject.Parse(jsonValue.ToString());
                            for (int m = 0; m < map.Count; m++)
                            {
                                var dynamicKey = map[mapkeys[m]].ToString();
                                if(obj.TryGetValue(dynamicKey, out JToken dynamicValue))
                                {
                                    string dynamicValueStr = dynamicValue.ToString();
                                    int cellIndex = staticColumnIndex + (z + 1) * (m + 1) * EveryColumnSkipNumber;
                                    var cell = row.CreateCell(cellIndex);
                                    cell.SetCellValue(dynamicValueStr);
                                }
                            }
                        }
                        catch
                        {
                            throw new Exception("Json转换失败!请确认字段是否正确！");
                        }
                    }
                }
                foreach (var sheetAction in SheetActionList)
                {
                    sheetAction.Invoke(sheet);
                }
                //执行对所有sheet都生效的合并操作
                foreach (var range in MergeRecord[-1])
                {
                    sheet.AddMergedRegion(range);
                }
                //执行对专属sheet生效的合并操作
                foreach (var range in MergeRecord[p-1])
                {
                    sheet.AddMergedRegion(range);
                }
                ExecuteComment();
            }
        }
        private void ExecuteComment()
        {
            IDrawing drawing = this.Sheet.CreateDrawingPatriarch();
            foreach(var commentModel in this.CommentList)
            {
                IRow row = this.Sheet.GetRow(commentModel.RowIndex);
                if (row == null)
                {
                    row= this.Sheet.CreateRow(commentModel.RowIndex);
                }
                ICell cell = row.GetCell(commentModel.ColumnIndex);
                if (cell == null)
                {
                    cell = row.CreateCell(commentModel.ColumnIndex);
                }
                IComment comment = cell.CellComment;
                if(comment == null)
                {
                    IClientAnchor clientAnchor = drawing.CreateAnchor(0, 0, 0, 3, 3, 3, 5, 6);
                    comment = drawing.CreateCellComment(clientAnchor);
                    comment.Visible = this.CommentVisible;
                    comment.String= this.Workbook.GetCreationHelper().CreateRichTextString(commentModel.Message);
                    cell.CellComment = comment;
                }
                else
                {
                    comment.String = this.Workbook.GetCreationHelper().CreateRichTextString(commentModel.Message + ";\n" + comment.String.String);
                }
                if (CommentCellStyle != null)
                {
                    cell.CellStyle = CommentCellStyle;
                }
            }
        }

        
    }
}
